﻿using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Runtime.CompilerServices;
using System.Threading.Tasks;
using Dapper;
using Oracle.ManagedDataAccess.Client;
using SqlKata.Compilers;
using Datory.Utils;

[assembly: InternalsVisibleTo("Datory.Tests")]

namespace Datory.DatabaseImpl
{
    internal class OracleImpl : IDatabaseImpl
    {
        private static IDatabaseImpl _instance;
        public static IDatabaseImpl Instance
        {
            get
            {
                if (_instance != null) return _instance;
                _instance = new OracleImpl();
                return _instance;
            }
        }

        public DbConnection GetConnection(string connectionString)
        {
            return new OracleConnection(connectionString);
        }

        public Compiler GetCompiler(string connectionString)
        {
            return new OracleCompiler
            {
                UseLegacyPagination = IsUseLegacyPagination(connectionString)
            };
        }

        public bool IsUseLegacyPagination(string connectionString)
        {
            return false;
        }

        public async Task<List<string>> GetDatabaseNamesAsync(string connectionString)
        {
            IEnumerable<string> databaseNames;

            using (var connection = GetConnection(connectionString))
            {
                try
                {
                    databaseNames = await connection.QueryAsync<string>("select name from v$database");
                }
                catch
                {
                    databaseNames = await connection.QueryAsync<string>("select ora_database_name from dual");
                }
            }

            return databaseNames != null ? databaseNames.Where(name => !string.IsNullOrEmpty(name)).ToList() : new List<string>();
        }

        public async Task<List<string>> GetTableNamesAsync(string connectionString)
        {
            IEnumerable<string> tableNames;

            using (var connection = GetConnection(connectionString))
            {
                var sqlString = "select TABLE_NAME from user_tables";

                tableNames = await connection.QueryAsync<string>(sqlString);
            }

            return tableNames != null ? tableNames.Where(tableName => !string.IsNullOrEmpty(tableName)).ToList() : new List<string>();
        }

        public string ColumnIncrement(string columnName, int plusNum = 1)
        {
            return $"COALESCE({columnName}, 0) + {plusNum}";
        }

        public string ColumnDecrement(string columnName, int minusNum = 1)
        {
            return $"COALESCE({columnName}, 0) - {minusNum}";
        }

        public string GetAutoIncrementDataType(bool alterTable = false)
        {
            return "NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY";
        }

        private string ToColumnString(DataType type, string attributeName, int length)
        {
            if (type == DataType.Boolean)
            {
                return $"{attributeName} number(1)";
            }
            if (type == DataType.DateTime)
            {
                return $"{attributeName} timestamp(6) with time zone";
            }
            if (type == DataType.Decimal)
            {
                return $"{attributeName} number(38, 2)";
            }
            if (type == DataType.Integer)
            {
                return $"{attributeName} number";
            }
            if (type == DataType.Text)
            {
                return $"{attributeName} nclob";
            }
            return $"{attributeName} nvarchar2({length})";
        }

        public string GetColumnSqlString(TableColumn tableColumn)
        {
            if (tableColumn.IsIdentity)
            {
                return $@"{tableColumn.AttributeName} {GetAutoIncrementDataType()}";
            }

            return ToColumnString(tableColumn.DataType, tableColumn.AttributeName, tableColumn.DataLength);
        }

        public string GetPrimaryKeySqlString(string tableName, string attributeName)
        {
            return $@"CONSTRAINT PK_{tableName}_{attributeName} PRIMARY KEY ({attributeName})";
        }

        public string GetQuotedIdentifier(string identifier)
        {
            return identifier;
        }

        private DataType ToDataType(string dataTypeStr)
        {
            if (string.IsNullOrEmpty(dataTypeStr)) return DataType.VarChar;

            var dataType = DataType.VarChar;

            dataTypeStr = Utilities.TrimAndToUpper(dataTypeStr);
            if (dataTypeStr.StartsWith("TIMESTAMP("))
            {
                dataType = DataType.DateTime;
            }
            else if (dataTypeStr == "NUMBER")
            {
                dataType = DataType.Integer;
            }
            else if (dataTypeStr == "NCLOB")
            {
                dataType = DataType.Text;
            }
            else if (dataTypeStr == "NVARCHAR2")
            {
                dataType = DataType.VarChar;
            }
            else if (dataTypeStr == "CLOB")
            {
                dataType = DataType.Text;
            }
            else if (dataTypeStr == "VARCHAR2")
            {
                dataType = DataType.VarChar;
            }

            return dataType;
        }

        public async Task<List<TableColumn>> GetTableColumnsAsync(string connectionString, string tableName)
        {
            var list = new List<TableColumn>();

            using (var connection = GetConnection(connectionString))
            {
                var owner = Utilities.GetConnectionStringUserName(connectionString).ToUpper();
                tableName = tableName.ToUpper();

                var sqlString =
                    $"SELECT COLUMN_NAME AS columnName, DATA_TYPE AS DataType, DATA_PRECISION AS DataPrecision, DATA_SCALE AS DataScale, CHAR_LENGTH AS CharLength, DATA_DEFAULT AS DataDefault FROM all_tab_cols WHERE OWNER = '{owner}' and table_name = '{tableName}' and user_generated = 'YES' ORDER BY COLUMN_ID";

                IEnumerable<dynamic> columns = await connection.QueryAsync<dynamic>(sqlString);

                foreach (var column in columns)
                {
                    var columnName = column.columnName;
                    var dataType = ToDataType(column.DataType);
                    var percision = column.DataPrecision;
                    var scale = column.DataScale;
                    var charLength = column.CharLength;
                    var dataDefault = column.DataDefault;

                    if (dataType == DataType.Integer)
                    {
                        if (scale == 2)
                        {
                            dataType = DataType.Decimal;
                        }
                        else if (percision == 1)
                        {
                            dataType = DataType.Boolean;
                        }
                    }
                    var isIdentity = dataDefault.Contains(".nextval");

                    var info = new TableColumn
                    {
                        AttributeName = columnName,
                        DataType = dataType,
                        DataLength = charLength,
                        IsPrimaryKey = false,
                        IsIdentity = isIdentity
                    };
                    list.Add(info);
                }

                sqlString =
                    $@"select distinct cu.column_name from all_cons_columns cu inner join all_constraints au 
on cu.constraint_name = au.constraint_name
and au.constraint_type = 'P' and cu.OWNER = '{owner}' and cu.table_name = '{tableName}'";

                var columnNames = connection.Query<string>(sqlString);
                foreach (var columnName in columnNames)
                {
                    foreach (var tableColumnInfo in list)
                    {
                        if (columnName != tableColumnInfo.AttributeName) continue;
                        tableColumnInfo.IsPrimaryKey = true;
                        break;
                    }
                }
            }

            return list;
        }

        public string GetAddColumnsSqlString(string tableName, string columnsSqlString)
        {
            return $"ALTER TABLE {GetQuotedIdentifier(tableName)} ADD {columnsSqlString}";
        }
    }
}
